package com.zn.opit.tech.easyexcel.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelUtil {

    /**
     * 不创建对象的写
     * @param headList
     * @param dataList
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, String filename, String sheetName,
                                   List<List<String>> headList, List<List<String>> dataList) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(out).registerWriteHandler(setHorizontalCellStyleStrategy()).head(headList).build();
        WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
        excelWriter.write(dataList, sheet);
        excelWriter.finish();
        byte[] content = out.toByteArray();
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename +".xlsx", "UTF-8"));
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString());
        response.setHeader("Content-Length", "" + content.length);
        OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
        outputStream.write(content);
        outputStream.close();
    }

    /**
     * 带对象的写
     * @param head
     * @param data
     * @param <T>
     * @throws IOException
     */
    public static <T> void exportExcel(HttpServletResponse response, String filename, String sheetName,
                                       Class<T> head, List<T> data) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(out, head).registerWriteHandler(setHorizontalCellStyleStrategy()).build();
        WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
        excelWriter.write(data, sheet);
        excelWriter.finish();
        byte[] content = out.toByteArray();
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename +".xlsx", "UTF-8"));
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString());
        response.setHeader("Content-Length", "" + content.length);
        OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
        outputStream.write(content);
        outputStream.close();
    }

    /**
     * excel  样式处理
     *
     * @return
     */
    private static HorizontalCellStyleStrategy setHorizontalCellStyleStrategy() {
        /*******自定义列标题和内容的样式******/
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 背景绿色
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 读excel，不限制条数
     * @param file  文件
     * @param clazz 读取成的对象
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz) throws IOException {
        return readExcel(file, -1, clazz);
    }

    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     *      读取成对象
     * @param file  文件
     * @param limitLines    限制条数
     * @param clazz     读取成的对象
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(MultipartFile file, int limitLines, Class<T> clazz) throws IOException {
        ExcelListener<T> excelListener = readExcelListener(file, limitLines, clazz);
        return excelListener.getDataList();
    }

    /**
     * 解析导入excel数据  读取成map
     * @param file  文件
     * @param lineLimit 限制条数
     * @return
     * @throws IOException
     */
    public static List<Map<Integer, String>> readExcel(MultipartFile file, Integer lineLimit) throws IOException {
        ExcelListener<Map<Integer, String>> excelListener = readExcelListener(file, lineLimit);
        return excelListener.getDataList();
    }

    /**
     * 读取excel对象 listener
     * @param file  文件
     * @param limitLines  限制条数
     * @param clazz
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> ExcelListener<T> readExcelListener(MultipartFile file, int limitLines, Class<T> clazz) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<T>(limitLines);
        return readExcelListener(file, clazz, excelListener);
    }

    /**
     * 读取excel map listener
     * @param file  文件
     * @param lineLimit 限制条数
     * @return
     * @throws IOException
     */
    public static ExcelListener<Map<Integer, String>> readExcelListener(MultipartFile file, Integer lineLimit) throws IOException {
        ExcelListener<Map<Integer, String>> excelListener = new ExcelListener<>(lineLimit);
        return readExcelListener(file, null, excelListener);
    }

    private static <T> ExcelListener<T> readExcelListener(MultipartFile file, Class<T> clazz, ExcelListener<T> excelListener) throws IOException {
        ExcelReader excelReader = getReader(file.getInputStream(), file.getOriginalFilename(), clazz, excelListener);
        if (excelReader == null) {
            return excelListener;
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet readSheet : readSheetList) {
            excelReader.read(readSheet);
        }
        excelReader.finish();
        return excelListener;
    }

    private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener<T> excelListener) {
        if (filename == null || !filename.toLowerCase().endsWith(".xlsx")) {
            return null;
        }
        try {
            ExcelReader excelReader = clazz == null ? EasyExcel.read(inputStream, excelListener).build() :
                    EasyExcel.read(inputStream, clazz, excelListener).build();
            inputStream.close();
            return excelReader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
